import random
import pandas as pd

def gp():
    df = pd.read_excel("csen.xlsx", engine='openpyxl')
    dict1 = df.set_index('选手ID').to_dict('index')
    players = []
    for i in range(8):
        C_P = [random.randint(0, 10) for _ in range(10)]
        vs = sorted(C_P)[1:-1]

        avg = sum(vs) / len(vs)
        im = 's0' + str(i + 1)
        player = {
            '选手ID': 's0' + str(i + 1),
            '姓名': dict1[im]['姓名'],
            '国籍': dict1[im]['国籍'],
            '难度系数': dict1[im]['难度系数'],
            '裁判打分': str(C_P)[1:-1],
            '平均分': round(avg, 2),
            '最后得分': round(avg * dict1[im]['难度系数'], 2)
        }
        players.append(player)
    return players

def xr(players):
    df = pd.DataFrame(players)
    df['名次'] = df['最后得分'].rank(method='min', ascending=False).astype(int)
    df = df.sort_values(by='最后得分', ascending=False).reset_index(drop=True)
    df = df[['选手ID', '姓名', '国籍', '难度系数', '裁判打分', '平均分', '最后得分', '名次']]
    filename = "成绩排名.xlsx"
    with pd.ExcelWriter(filename, engine='openpyxl') as writer:
        df.to_excel(writer, index=False, sheet_name='选手成绩')
    print(f"文件已保存：{filename}")


xr(gp())
